package com.alongstudio.account.dao.base.impl;

import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.stereotype.Repository;

import com.alongstudio.account.commons.utils.AnnotationUtil;
import com.alongstudio.account.commons.utils.BeanUtil;
import com.alongstudio.account.commons.utils.PatternUtil;
import com.alongstudio.account.dao.base.IBaseDao;

@Repository("BaseDao")
public class BaseDao<T> extends HibernateDaoSupport implements IBaseDao<T> {

	private static Logger log = LoggerFactory.getLogger("changde-parking");

	@Resource(name = "sessionFactory")
	public void setMySessionFactory(SessionFactory sessionFactory) {
		this.setSessionFactory(sessionFactory);
	}
	
	/**
	 * 插入数据
	 */
	@Override
	public void create(T entity) {
		getHibernateTemplate().save(entity);
	}
	
	/**
	 * 批量插入数据
	 */
	@Override
	public void batchCreate(List<T> results){
		for(T object:results){
			create(object);
		}
	}
	
	/**
	 * 非覆盖更新
	 */
	@Override
	public void update(T entity) {
		Object target = this.queryByPk(entity.getClass(), AnnotationUtil
				.getPkValue(entity));
		BeanUtil.copyProperties(entity, target, false);
		getHibernateTemplate().update(target);
	}

	/**
	 * 批量非覆盖更新
	 */
	@Override
	public void batchUpdate(List<T> entities) {
		for(T entity:entities){
			update(entity);
		}
	}
	
	/**
	 * 覆盖更新
	 */
	@Override
	public void updateEx(T entity) {
//		Object target = this.queryByPk(entity.getClass(), AnnotationUtil
//				.getPkValue(entity));
//		BeanUtil.copyProperties(entity, target, true);
		getHibernateTemplate().update(entity);
	}
	
	/**
	 * 批量覆盖更新
	 */
	@Override
	public void batchExUpdate(List<T> entities) {
		for(T entity:entities){
			updateEx(entity);
		}
	}

	@Override
	public void delete(T entity) {
		getHibernateTemplate().delete(entity);
	}
	@Override
	public void deleteII(List<T> entities) {
		getHibernateTemplate().deleteAll(entities);
	}

	/**
	 * 根据主键删除对象
	 * @param c 类对象
	 * @param pk 对象主键
	 */
	@Override
	public void deleteByPk(Class c, Object pk) {
		getHibernateTemplate().delete(this.queryByPk(c, pk));
	}
	
	/**
	 * 根据主键删除对象
	 * @param c 类对象
	 * @param pk 对象主键集合
	 */
	@Override
	public void deleteByPkII(Class c, List<Object> PK) {
		List entities = new ArrayList();
		for (int i = 0; i < PK.size(); i++) {
			Object entity = this.queryByPk(c, PK.get(i));
			if(entity != null){
				entities.add(entity);
			}
		}
		this.deleteII(entities);
	}

	/**
	 * 根据主键查找对象
	 * @param c 类对象
	 * @param pk 对象主键
	 */
	@Override
	public Object queryByPk(Class c, Object pk) {
		return getHibernateTemplate().get(c, AnnotationUtil.getPkObject(c, pk.toString()));
	}
	
	@Override
	public T queryById(Class<T> t, String id) {
		T result =this.getHibernateTemplate().get(t, id);
		return result;
	}

//	@Override
//	public List queryByPkII(Class c, List pk) {
//		List result = new ArrayList();
//		for (int j = 0; j < pk.size(); j++) {
//			result.add(this.queryByPk(c, pk.get(j)));
//		}
//		return result;
//	}
	
	/**
	 * 通过hql查询实体对象
	 * @param hql 
	 */
	public List queryByHql(String hql) {
		return getHibernateTemplate().find(hql);
	}
	
	/**
	 * 通过hql进行分页查询
	 * @param hql
	 * @param start  起始位置
	 * @param pageSize 分页大小
	 */
	public List queryByHql(final String hql,final int start, final int pageSize) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				if (pageSize > 0) {
					query.setFirstResult(start);
					query.setMaxResults(pageSize);
				}
				List list = query.list();
				return list;
			}

		});
	}
	
	/**
	 * 通过hql进行分页查询
	 * @param hql
	 * @param start  起始位置
	 * @param pageSize 分页大小
	 * @param clazz 类对象
	 */
	public List queryBySql(final String sql,final int start, final int pageSize,final Class clazz) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = null;
				if(clazz!=null){
					 query=session.createSQLQuery(sql).addEntity(clazz);
				}else{
					 query=session.createSQLQuery(sql);
				}
				if (pageSize > 0) {
					query.setFirstResult(start);
					query.setMaxResults(pageSize);
				}
				List list = query.list();
				return list;
			}

		});
	}
	
	/**
	 * 根据集合中参数顺序进行条件更新
	 * @param sql
	 * @param params 查询参数
	 * @return 更新数量
	 */
	public int updateBySql(final String sql,final List params ) {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createSQLQuery(sql);
				if(params!=null){
					for (int i = 0, size = params.size(); i < size; i++) {
						Class type = params.get(i).getClass();
						if(type == java.util.Date.class){
							query.setTimestamp(i, (Date)params.get(i));
						}else if(type == java.sql.Date.class ){
							query.setTimestamp(i,  new Date(((java.sql.Date)params.get(i)).getTime()));
						}else if(type == java.sql.Timestamp.class){
							query.setTimestamp(i, (Timestamp)params.get(i));
						}else{
							query.setParameter(i, params.get(i));
						}
					}
				}			
				return query.executeUpdate();			
			}

		});
	}
	
	public int deleteByHql(String hql){
		return updateByHql(hql);
	}
	/**
	 * 根据HQL更新
	 * @param hql
     * @return 更新数量
	 */
	public int updateByHql(final String hql) {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				return query.executeUpdate();			
			}
		});
	}
	

	/**
	 * 根据集合中参数顺序进行条件更新
	 * @param hql
	 * @param params 查询参数
	 * @return 更新数量
	 */
	public int updateByHql(final String hql,final List params ) {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				if(params!=null){
					for (int i = 0, size = params.size(); i < size; i++) {
						Class type = params.get(i).getClass();
						if(type == java.util.Date.class){
							query.setTimestamp(i, (Date)params.get(i));
						}else if(type == java.sql.Date.class ){
							query.setTimestamp(i,  new Date(((java.sql.Date)params.get(i)).getTime()));
						}else if(type == java.sql.Timestamp.class){
							query.setTimestamp(i, (Timestamp)params.get(i));
						}else{
							query.setParameter(i, params.get(i));
						}
					}
				}			
				return query.executeUpdate();			
			}

		});
	}
	
	/**
	 * 根据集合中参数顺序进行条件更新
	 * @param hql
	 * @param params 查询参数
	 * @return 更新数量
	 */
	public int updateByHql(final String hql,final Object value ) {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				if(value!=null){				
					Class type = value.getClass();
					if(type == java.util.Date.class){
						query.setTimestamp(0, (Date)value);
					}else if(type == java.sql.Date.class ){
							query.setTimestamp(0,  new Date(((java.sql.Date)value).getTime()));
					}else if(type == java.sql.Timestamp.class){
							query.setTimestamp(0, (Timestamp)value);
					}else{
						query.setParameter(0, value);
					}				
				}			
				return query.executeUpdate();			
			}

		});
	}
	
	/**
	 * 根据Map中参数进行条件更新
	 * @param hql
	 * @param map 查询参数
	 * @return 更新数量
	 */
	public int updateByHql(final String hql,final Map map ){
		final Map<String,Object> mapCopy = getMapCopy(map);
		final String hqlUpdate=filterQuery(hql, mapCopy);	
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hqlUpdate);
				if(mapCopy!=null){
					Iterator iter=mapCopy.entrySet().iterator();
					while(iter.hasNext()){
						Map.Entry entry=(Map.Entry)iter.next();
						//获取key
						String key=(String)entry.getKey();
						//获取value
						Object value= entry.getValue();
						if(value!=null && hqlUpdate.indexOf(":"+key)>=0){
							Class type = value.getClass();
							if(type == java.util.Date.class){
								query.setTimestamp(key, (Date)value);
							}else if(type == java.sql.Date.class ){
								query.setTimestamp(key,  new Date(((java.sql.Date)value).getTime()));
							}else if(type == java.sql.Timestamp.class){
								query.setTimestamp(key, (Timestamp)value);
							}else if(value instanceof java.util.List){
								query.setParameterList(key, (Collection) value);
							}else{
								query.setParameter(key, value);
							}
						}	
					}
				}			
				return query.executeUpdate();			
			}
		});
	}
	
	/**
	 * 根据Map中参数进行条件更新
	 * @param sql
	 * @param map 查询参数
	 * @return 更新数量
	 */
	public int updateBySql(final String sql,final Map map ){
		final Map<String,Object> mapCopy = getMapCopy(map);
		final String sqlUpdate=filterQuery(sql, mapCopy);
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createSQLQuery(sqlUpdate);
				if(mapCopy!=null){
					Iterator iter=mapCopy.entrySet().iterator();
					while(iter.hasNext()){
						Map.Entry entry=(Map.Entry)iter.next();
						//获取key
						String key=(String)entry.getKey();
						//获取value
						Object value= entry.getValue();
						if(value!=null && sqlUpdate.indexOf(":"+key)>=0){
							Class type = value.getClass();
							if(type == java.util.Date.class){
								query.setTimestamp(key, (Date)value);
							}else if(type == java.sql.Date.class ){
								query.setTimestamp(key,  new Date(((java.sql.Date)value).getTime()));
							}else if(type == java.sql.Timestamp.class){
								query.setTimestamp(key, (Timestamp)value);
							}else if(value instanceof java.util.List){
								query.setParameterList(key, (Collection) value);
							}else{
								query.setParameter(key, value);
							}
						}			
					}
				}			
				return query.executeUpdate();			
			}

		});
	}
	
	/**
	 * 通过hql和一个条件参数进行数据查询
	 * @param hql
	 * @param value 查询条件
	 * @return 数据集合
	 */
	public List queryByHql(String hql, Object value) {
		List list = getHibernateTemplate().find(hql, value);
		return list;
	}
	

	/**
	 * 通过hql和一个条件参数进行数据查询
	 * @param hql
	 * @param value 查询条件数组
	 * @return 数据集合
	 */
	public List queryByHql(String hql, Object[] values) {
		return getHibernateTemplate().find(hql, values);
	}

	/**
	 * 根据Map中参数进行条件查询
	 * @param hql
	 * @param map 查询参数
	 * @return 查询结果
	 */
	public List queryByHql(final String hql, final Map map) {
		final Map<String,Object> mapCopy = getMapCopy(map);
		final String hqlUpdate=filterQuery(hql, mapCopy);
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hqlUpdate);
				if(mapCopy!=null){
					Iterator iter=mapCopy.entrySet().iterator();
					while(iter.hasNext()){
						Map.Entry entry=(Map.Entry)iter.next();
						//获取key
						String key=(String)entry.getKey();
						//获取value
						Object value= entry.getValue();
						if(value!=null && hqlUpdate.indexOf(":"+key)>=0){
							Class type = value.getClass();
							if(type == java.util.Date.class){
								query.setTimestamp(key, (Date)value);
							}else if(type == java.sql.Date.class ){
								query.setTimestamp(key,  new Date(((java.sql.Date)value).getTime()));
							}else if(type == java.sql.Timestamp.class){
								query.setTimestamp(key, (Timestamp)value);
							}else if(value instanceof java.util.List){
								query.setParameterList(key, (Collection) value);
							}else{
								query.setParameter(key, value);
							}
						}				
					}
				}	
				List list = query.list();
				return list;
			}

		});
	}
	

	/**
	 * 根据Map中参数进行条件查询
	 * @param sql
	 * @param map 查询参数
	 * @param clazz 类对象
	 * @return 查询结果
	 */
	public List queryBySql(final String sql,final Map map,final Class clazz) {
		final Map<String,Object> mapCopy = getMapCopy(map);
		final String sqlUpdate=filterQuery(sql, mapCopy);
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = null;
				if(clazz!=null){
					 query=session.createSQLQuery(sqlUpdate).addEntity(clazz);
				}else{
					 query=session.createSQLQuery(sqlUpdate);
				}
				if(mapCopy!=null){
					Iterator iter=mapCopy.entrySet().iterator();
					while(iter.hasNext()){
						Map.Entry entry=(Map.Entry)iter.next();
						//获取key
						String key=(String)entry.getKey();
						//获取value
						Object value= entry.getValue();
						if(value!=null && sqlUpdate.indexOf(":"+key)>=0){
							Class type = value.getClass();
							if(type == java.util.Date.class){
								query.setTimestamp(key, (Date)value);
							}else if(type == java.sql.Date.class ){
								query.setTimestamp(key,  new Date(((java.sql.Date)value).getTime()));
							}else if(type == java.sql.Timestamp.class){
								query.setTimestamp(key, (Timestamp)value);
							}else if(value instanceof java.util.List){
								query.setParameterList(key, (Collection) value);
							}else{
								query.setParameter(key, value);
							}
						}				
					}
				}	
				List list = query.list();
				return list;
			}

		});			
	}
	
	/**
	 * 根据Map中参数进行条件查询
	 * @param hql
	 * @param map 查询参数
	 * @param start 起始位置
	 * @param pageSize 分页大小
	 * @return 查询结果
	 */
	public List queryByHql(final String hql, final Map map, final int start, final int pageSize) {
		final Map<String,Object> mapCopy = getMapCopy(map);
		final String hqlUpdate = filterQuery(hql, mapCopy);
		
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hqlUpdate);
				if(mapCopy!=null){
					Iterator iter=mapCopy.entrySet().iterator();
					while(iter.hasNext()){
						Map.Entry entry=(Map.Entry)iter.next();
						//获取key
						String key=(String)entry.getKey();
						//获取value
						Object value= entry.getValue();
						if(value!=null && hqlUpdate.indexOf(":"+key)>=0){
							Class type = value.getClass();
							if(type == java.util.Date.class){
								query.setTimestamp(key, (Date)value);
							}else if(type == java.sql.Date.class ){
								query.setTimestamp(key,  new Date(((java.sql.Date)value).getTime()));
							}else if(type == java.sql.Timestamp.class){
								query.setTimestamp(key, (Timestamp)value);
							}else if(value instanceof java.util.List){
								query.setParameterList(key, (Collection) value);
							}else{
								query.setParameter(key, value);
							}
						}				
					}
				}	
				
				if (pageSize > 0) {
					query.setFirstResult(start);
					query.setMaxResults(pageSize);
				}
				List list = query.list();
				return list;
			}

		});
		
	}

	/**
	 * 根据Map中参数进行条件查询
	 * @param sql
	 * @param map 查询参数
	 * @param start 起始位置
	 * @param pageSize 分页大小
	 * @param clazz 类对象
	 * @return 查询结果
	 */
	public List queryBySql(final String sql,final Map map,final  int start,final int pageSize,final Class clazz) {
		final Map<String,Object> mapCopy = getMapCopy(map);
		final String sqlUpdate=filterQuery(sql, mapCopy);
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = null;
				if(clazz!=null){
					 query=session.createSQLQuery(sqlUpdate).addEntity(clazz);
				}else{
					 query=session.createSQLQuery(sqlUpdate);
				}
				if(mapCopy!=null){
					Iterator iter=mapCopy.entrySet().iterator();
					while(iter.hasNext()){
						Map.Entry entry=(Map.Entry)iter.next();
						//获取key
						String key=(String)entry.getKey();
						//获取value
						Object value= entry.getValue();
						if(value!=null && sqlUpdate.indexOf(":"+key)>=0){
							Class type = value.getClass();
							if(type == java.util.Date.class){
								query.setTimestamp(key, (Date)value);
							}else if(type == java.sql.Date.class ){
								query.setTimestamp(key,  new Date(((java.sql.Date)value).getTime()));
							}else if(type == java.sql.Timestamp.class){
								query.setTimestamp(key, (Timestamp)value);
							}else if(value instanceof java.util.List){
								query.setParameterList(key, (Collection) value);
							}else{
								query.setParameter(key, value);
							}
						}				
					}
				}	
				
				if (pageSize > 0) {
					query.setFirstResult(start);
					query.setMaxResults(pageSize);
				}
				List list = query.list();
				return list;
			}

		});
		
	}
	
	
	public Object queryObjectByHql(String hql,Map map){
		final Map<String,Object> mapCopy = getMapCopy(map);
		final String hqlUpdate=filterQuery(hql, mapCopy);
		return getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hqlUpdate);
				if(mapCopy!=null){
					Iterator iter=mapCopy.entrySet().iterator();
					while(iter.hasNext()){
						Map.Entry entry=(Map.Entry)iter.next();
						//获取key
						String key=(String)entry.getKey();
						//获取value
						Object value= entry.getValue();
						if(value!=null && hqlUpdate.indexOf(":"+key)>=0){
							Class type = value.getClass();
							if(type == java.util.Date.class){
								query.setTimestamp(key, (Date)value);
							}else if(type == java.sql.Date.class ){
								query.setTimestamp(key,  new Date(((java.sql.Date)value).getTime()));
							}else if(type == java.sql.Timestamp.class){
								query.setTimestamp(key, (Timestamp)value);
							}else if(value instanceof java.util.List){
								query.setParameterList(key, (Collection) value);
							}else{
								query.setParameter(key, value);
							}
						}				
					}
				}	
				return query.uniqueResult();
			}

		});
	}
	
	
	public Object queryObjectBySql(String sql,Map map,final Class clazz){
		final Map<String,Object> mapCopy = getMapCopy(map);
		final String sqlUpdate=filterQuery(sql, mapCopy);
		return getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = null;
				if(clazz!=null){
					 query=session.createSQLQuery(sqlUpdate).addEntity(clazz);
				}else{
					 query=session.createSQLQuery(sqlUpdate);
				}
				if(mapCopy!=null){
					Iterator iter=mapCopy.entrySet().iterator();
					while(iter.hasNext()){
						Map.Entry entry=(Map.Entry)iter.next();
						//获取key
						String key=(String)entry.getKey();
						//获取value
						Object value= entry.getValue();
						if(value!=null && sqlUpdate.indexOf(":"+key)>=0){
							Class type = value.getClass();
							if(type == java.util.Date.class){
								query.setTimestamp(key, (Date)value);
							}else if(type == java.sql.Date.class ){
								query.setTimestamp(key,  new Date(((java.sql.Date)value).getTime()));
							}else if(type == java.sql.Timestamp.class){
								query.setTimestamp(key, (Timestamp)value);
							}else if(value instanceof java.util.List){
								query.setParameterList(key, (Collection) value);
							}else{
								query.setParameter(key, value);
							}
						}				
					}
				}	
				return query.uniqueResult();
			}

		});			
	}
	
	
	public Object queryObjectByHql(final String hql,final Object value){
		return getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				if(value!=null){
					Class type = value.getClass();
					if(type == java.util.Date.class){
						query.setTimestamp(0, (Date)value);
					}else if(type == java.sql.Date.class ){
						query.setTimestamp(0,  new Date(((java.sql.Date)value).getTime()));
					}else if(type == java.sql.Timestamp.class){
						query.setTimestamp(0, (Timestamp)value);
					}else{
						query.setParameter(0, value);
					}
				}				
				return query.uniqueResult();
			}
		});
	}
	
	public Object queryObjectByHql(final String hql){
		return getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				return query.uniqueResult();
			}
		});
	}
	
	
	/**
	 * 数据统计
	 * @Title: count
	 * @Description: TODO
	 * @param @param hql 统计的sql
	 * @param @param params 参数
	 * @param @return    
	 * @return Integer    
	 * @throws
	 */
	public Integer countByHql(final String hql, final Object value) {
		HibernateCallback callback=new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				if(value!=null){
					Class type = value.getClass();
					if(type == java.util.Date.class){
						query.setTimestamp(0, (Date)value);
					}else if(type == java.sql.Date.class ){
						query.setTimestamp(0,  new Date(((java.sql.Date)value).getTime()));
					}else if(type == java.sql.Timestamp.class){
						query.setTimestamp(0, (Timestamp)value);
					}else{
						query.setParameter(0, value);
					}
				}	
				return query.uniqueResult();
			}
		};
		Object obj=this.getHibernateTemplate().execute(callback);
		if(obj!=null){
			if(obj instanceof Integer){
				return (Integer)obj;
			}else{
				return ((Long)obj).intValue();
			}
				
		}
		return 0;
	}
	
	/**
	 * 数据统计
	 * @Title: count
	 * @Description: TODO
	 * @param @param hql 统计的sql
	 * @param @param params 参数
	 * @param @return    
	 * @return Integer    
	 * @throws
	 */
	public Integer countByHql(final String hql) {
		HibernateCallback callback=new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				return query.uniqueResult();
			}
		};
		Object obj=this.getHibernateTemplate().execute(callback);
		if(obj!=null){
			if(obj instanceof Integer){
				return (Integer)obj;
			}else{
				return ((Long)obj).intValue();
			}
				
		}
		return 0;
	}
	
	/**
	 * 数据统计
	 * @Title: count
	 * @Description: TODO
	 * @param @param hql 统计的sql
	 * @param @param params 参数
	 * @param @return    
	 * @return Integer    
	 * @throws
	 */
	public Integer countByHql(final String hql, final Map map) {
		final Map<String,Object> mapCopy = getMapCopy(map);
		final String hqlUpdate=filterQuery(hql, mapCopy);
		HibernateCallback callback=new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hqlUpdate);
				if(mapCopy!=null){
					Iterator iter=mapCopy.entrySet().iterator();
					while(iter.hasNext()){
						Map.Entry entry=(Map.Entry)iter.next();
						//获取key
						String key=(String)entry.getKey();
						//获取value
						Object value= entry.getValue();
						if(value!=null && hqlUpdate.indexOf(":"+key)>=0){
							Class type = value.getClass();
							if(type == java.util.Date.class){
								query.setTimestamp(key, (Date)value);
							}else if(type == java.sql.Date.class ){
								query.setTimestamp(key,  new Date(((java.sql.Date)value).getTime()));
							}else if(type == java.sql.Timestamp.class){
								query.setTimestamp(key, (Timestamp)value);
							}else if(value instanceof java.util.List){
								query.setParameterList(key, (Collection) value);
							}else{
								query.setParameter(key, value);
							}
						}			
					}
				}	
				return query.uniqueResult();
			}
		};
		Object obj=this.getHibernateTemplate().execute(callback);
		if(obj!=null){
			if(obj instanceof Integer){
				return (Integer)obj;
			}else{
				return ((Long)obj).intValue();
			}
				
		}
		return 0;
	}


	public Integer countBySql(final String sql, final Map map) {
		final Map<String,Object> mapCopy = getMapCopy(map);
		final String sqlUpdate=filterQuery(sql, mapCopy);
		HibernateCallback callback=new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createSQLQuery(sqlUpdate);
				if(mapCopy!=null){
					Iterator iter=mapCopy.entrySet().iterator();
					while(iter.hasNext()){
						Map.Entry entry=(Map.Entry)iter.next();
						//获取key
						String key=(String)entry.getKey();
						//获取value
						Object value= entry.getValue();
						if(value!=null && sqlUpdate.indexOf(":"+key)>=0){
							Class type = value.getClass();
							if(type == java.util.Date.class){
								query.setTimestamp(key, (Date)value);
							}else if(type == java.sql.Date.class ){
								query.setTimestamp(key,  new Date(((java.sql.Date)value).getTime()));
							}else if(type == java.sql.Timestamp.class){
								query.setTimestamp(key, (Timestamp)value);
							}else if(value instanceof java.util.List){
								query.setParameterList(key, (Collection) value);
							}else{
								query.setParameter(key, value);
							}
						}				
					}
				}	
				return query.uniqueResult();
			}		
		};
		Object obj=this.getHibernateTemplate().execute(callback);
		if(obj!=null){
			return ((BigDecimal)obj).intValue();
		}
		return 0;
		
	}

	
	public List queryByHql(final String hql, final List params) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				if(params!=null){
					for (int i = 0, size = params.size(); i < size; i++) {
						Class type = params.get(i).getClass();
						if(type == java.util.Date.class){
							query.setTimestamp(i, (Date)params.get(i));
						}else if(type == java.sql.Date.class ){
							query.setTimestamp(i,  new Date(((java.sql.Date)params.get(i)).getTime()));
						}else if(type == java.sql.Timestamp.class){
							query.setTimestamp(i, (Timestamp)params.get(i));
						}else{
							query.setParameter(i, params.get(i));
						}
					}
				}			
				List list = query.list();
				return list;
			}

		});
	}

	public List queryBySql(final String sql, final List params,final Class clazz) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = null;
				if(clazz!=null){
					 query=session.createSQLQuery(sql).addEntity(clazz);
				}else{
					 query=session.createSQLQuery(sql);
				}
				if(params!=null){
					for (int i = 0, size = params.size(); i < size; i++) {
						Class type = params.get(i).getClass();
						if(type == java.util.Date.class){
							query.setTimestamp(i, (Date)params.get(i));
						}else if(type == java.sql.Date.class ){
							query.setTimestamp(i,  new Date(((java.sql.Date)params.get(i)).getTime()));
						}else if(type == java.sql.Timestamp.class){
							query.setTimestamp(i, (Timestamp)params.get(i));
						}else{
							query.setParameter(i, params.get(i));
						}
					}
				}			
				List list = query.list();
				return list;
			}

		});
	}
	
	
	public List queryByHql(final String hql, final List params, final int start, final int pageSize) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				if(params!=null){
					for (int i = 0, size = params.size(); i < size; i++) {
						Class type = params.get(i).getClass();
						if(type == java.util.Date.class){
							query.setTimestamp(i, (Date)params.get(i));
						}else if(type == java.sql.Date.class ){
							query.setTimestamp(i,  new Date(((java.sql.Date)params.get(i)).getTime()));
						}else if(type == java.sql.Timestamp.class){
							query.setTimestamp(i, (Timestamp)params.get(i));
						}else{
							query.setParameter(i, params.get(i));
						}
					}
				}
				
				if (pageSize > 0) {
					query.setFirstResult(start);
					query.setMaxResults(pageSize);
				}
				List list = query.list();
				return list;
			}

		});
		
	}

	public List queryBySql(final String sql, final List params, final int start, final int pageSize,final Class clazz) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = null;
				if(clazz!=null){
					 query=session.createSQLQuery(sql).addEntity(clazz);
				}else{
					 query=session.createSQLQuery(sql);
				}
				if(params!=null){
					for (int i = 0, size = params.size(); i < size; i++) {
						Class type = params.get(i).getClass();
						if(type == java.util.Date.class){
							query.setTimestamp(i, (Date)params.get(i));
						}else if(type == java.sql.Date.class ){
							query.setTimestamp(i,  new Date(((java.sql.Date)params.get(i)).getTime()));
						}else if(type == java.sql.Timestamp.class){
							query.setTimestamp(i, (Timestamp)params.get(i));
						}else{
							query.setParameter(i, params.get(i));
						}
					}
				}
				
				if (pageSize > 0) {
					query.setFirstResult(start);
					query.setMaxResults(pageSize);
				}
				List list = query.list();
				return list;
			}

		});
		
	}
	

	/**
	 * 数据统计
	 * @Title: count
	 * @Description: TODO
	 * @param @param hql 统计的sql
	 * @param @param params 参数
	 * @param @return    
	 * @return Integer    
	 * @throws
	 */
	public Integer countByHql(final String hql, final List params) {

		HibernateCallback callback=new HibernateCallback() {

			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				if(params!=null){
					for (int i = 0, size = params.size(); i < size; i++) {
						Class type = params.get(i).getClass();
						if(type == java.util.Date.class){
							query.setTimestamp(i, (Date)params.get(i));
						}else if(type == java.sql.Date.class ){
							query.setTimestamp(i,  new Date(((java.sql.Date)params.get(i)).getTime()));
						}else if(type == java.sql.Timestamp.class){
							query.setTimestamp(i, (Timestamp)params.get(i));
						}else{
							query.setParameter(i, params.get(i));
						}
					}
				}
				return query.uniqueResult();
			}
		};
		
		return ((Long)this.getHibernateTemplate().execute(callback)).intValue();
		
	}


	public Integer countBySql(final String sql, final List params) {
		
		HibernateCallback callback=new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createSQLQuery(sql);
				if(params!=null){
					for (int i = 0, size = params.size(); i < size; i++) {
						Class type = params.get(i).getClass();
						if(type == java.util.Date.class){
							query.setTimestamp(i, (Date)params.get(i));
						}else if(type == java.sql.Date.class ){
							query.setTimestamp(i,  new Date(((java.sql.Date)params.get(i)).getTime()));
						}else if(type == java.sql.Timestamp.class){
							query.setTimestamp(i, (Timestamp)params.get(i));
						}else{
							query.setParameter(i, params.get(i));
						}
					}
				}
				return query.uniqueResult();
			}		
		};
		return ((Long)this.getHibernateTemplate().execute(callback)).intValue();
		
	}

	/**
	 * 执行sql和hql的封装过滤
	 * @param sql
	 * @param map
	 * @return
	 */
	/**
	 * select * from order where 1=1 and [prize=#obj1#] and [id=#id#] and type like #%aaa%#;
	 */
	private String filterQuery(String sql,Map<String,Object> map){
		//获取SQL中[prize=#obj1#]中的prize=#obj1# 
		List<String> conditions = PatternUtil.parserStringList(sql,"\\[num_of_counter\\]");
		for(String condition:conditions){
			//获取属性变量名称入obj1
			String attri=PatternUtil.parserString(condition, "#num_of_counter#");
			//去掉like语句中的%
			attri=attri.replaceAll("%", "");
			//将条件中[]不定条件中用不到的条件删除
			if(map.get(attri.trim())==null || (map.get(attri.trim()) instanceof String && StringUtils.isEmpty((String)map.get(attri.trim())))
					|| (map.get(attri.trim()) instanceof java.util.List && ((List)map.get(attri.trim())).isEmpty())){
				condition=condition.replaceAll("\\(", "\\\\(").replaceAll("\\)", "\\\\)");
				if(PatternUtil.isContain(sql, "((or)|(and))\\s+\\["+condition+"\\]")){
					sql=sql.replaceFirst("((or)|(and))\\s+\\["+condition+"\\]", "");
				}else if(PatternUtil.isContain(sql, "((or)|(and))\\s+\\(\\s*\\["+condition+"\\]")){
					sql=sql.replaceFirst("((or)|(and))\\s+\\(\\s*\\["+condition+"\\]", "(");
				}
				map.remove(attri.trim());
			}
		}
		//去除中括号
		sql=sql.replaceAll(("(\\[)|(\\])"), "");
		//去除( )格式的符号
		sql=sql.replaceAll(("\\(\\s*\\)"), "");
		List<String> attris = PatternUtil.parserStringList(sql,"#num_of_counter#");
		for(String attri:attris){
			String oldAttri=attri;
			if(attri.matches("%\\s*\\w+\\s*%")){		
				attri=attri.replaceAll("%", "");
//				if(((String)(map.get(attri.trim()))).indexOf("%")<0){
				map.put(attri.trim(), "%"+map.get(attri.trim())+"%");
//				}		
			}else if(attri.matches("\\s*\\w+\\s*%")){
				attri=attri.replaceAll("%", "");
				map.put(attri.trim(), map.get(attri.trim())+"%");
			}else if(attri.matches("%\\s*\\w+\\s*")){
				attri=attri.replaceAll("%", "");
				map.put(attri.trim(), "%"+map.get(attri.trim()));
			}
			sql=sql.replaceAll("#"+oldAttri+"#", ":"+attri.trim());
		}
		return sql;
	}
	
//	private String filterQuery(String sql,Map<String,Object> map,List results){
//		List<String> conditions = PatternUtil.parserStringList(sql,"\\[num_of_counter\\]");
//		for(String condition:conditions){
//			String attri=PatternUtil.parserString(condition, "#num_of_counter#");
//			attri=attri.replaceAll("%", "");
//			if(map.get(attri.trim())==null || (map.get(attri.trim()) instanceof String && StringUtils.isEmpty((String)map.get(attri.trim())))){
//				if(PatternUtil.isContain(sql, "((or)|(and))\\s+\\["+condition+"\\]")){
//					sql=sql.replaceFirst("((or)|(and))\\s+\\["+condition+"\\]", "");
//				}else if(PatternUtil.isContain(sql, "((or)|(and))\\s+\\(\\s*\\["+condition+"\\]")){
//					sql=sql.replaceFirst("((or)|(and))\\s+\\(\\s*\\["+condition+"\\]", "(");
//				}
//			}
//		}
//		//去除中括号
//		sql=sql.replaceAll(("(\\[)|(\\])"), "");
//		//去除( )格式的符号
//		sql=sql.replaceAll(("\\(\\s*\\)"), "");
//		List<String> attris = PatternUtil.parserStringList(sql,"#num_of_counter#");
//		int i=0;
//		for(String attri:attris){
//			if(attri.trim().matches("%\\s*\\w+\\s*%")){
//				attri=attri.replaceAll("%", "");
//				results.add("%"+map.get(attri.trim())+"%");
//			}else if(map.get(attri.trim()) instanceof java.util.List){
//				sql=sql.replaceAll("#"+attri+"#", ":list"+i);
//				results.add(map.get(attri.trim()));
//			}else{
//				results.add(map.get(attri.trim()));
//			}
//			i++;
//		}
//		
//		sql=sql.replaceAll("#(%)*\\w+(%)*#", "?");
//		return sql;
//	}
	
	/**
	 * 根据hql返回字段别名
	 * @Title: filterAlias
	 * @Description: TODO
	 * @param @param sql
	 * @param @return    
	 * @return List<String>    
	 * @throws
	 */
	private List<String> filterAlias(String sql){
		List<String> results=new ArrayList<String>();
		String aliasString=PatternUtil.parserString(PatternUtil.getContentBetweenWithNearest(sql, "select", "from"), "selectnum_of_counterfrom")+",";
		String [] aliasArray=aliasString.split("as");
		for(String alias:aliasArray){
			String attri;
			//如果有别名
			attri=PatternUtil.getContentBetweenWithNearest(alias, "", ",").replaceAll(",", "").trim();
			results.add(attri);
		}
		results.remove(0);
		return results;
	}
	
	/**
	 * 获取map条件集合的拷贝
	 * @Title: getMapCopy
	 * @Description: TODO
	 * @param @param map
	 * @param @return    
	 * @return Map    
	 * @throws
	 */
	private Map<String,Object> getMapCopy(Map map){
		Map<String,Object> mapCopy = null;
		if(map!=null){
			mapCopy = new HashMap<String,Object>();
			mapCopy.putAll(map);
		}
		return mapCopy;
	}
	
	private <T> List<T> reflectSetMethod(List<Object[]> results,Class<T> t,List<String> alias){
		List<T> list=new ArrayList<T>();
		try{
			for(Object[] objs:results){//每条记录
				T instance=t.newInstance();
				for(int i=0;i<objs.length;i++){//每条记录中的属性
					Method method=null;
					String field=alias.get(i);
					//首字母大写
					field=field.substring(0,1).toUpperCase() + field.substring(1);
					if(objs[i] instanceof String){					
						method = t.getDeclaredMethod("set"+field, String.class);
					}else if(objs[i] instanceof Integer ){
						method = t.getDeclaredMethod("set"+field, Integer.class);
					}else if(objs[i] instanceof Date){
						method = t.getDeclaredMethod("set"+field, Date.class);
					}else if(objs[i] instanceof Long){
						method = t.getDeclaredMethod("set"+field, Long.class);
					}else if(objs[i] instanceof Double){
						method = t.getDeclaredMethod("set"+field, Double.class);
					}else if(objs[i] instanceof Float){
						method = t.getDeclaredMethod("set"+field, Float.class);
					}
					if(method!=null){
						method.invoke(instance, objs[i]);
					}
				}
				list.add(instance);			
			}
		}catch(Exception e){
			e.printStackTrace();
		}	
		return list;
	}
	
	
	/**
	 * 根据主键查找对象
	 * @param t 类泛型
	 * @param hql 
	 */
	public <T> List<T> queryVoByHql(Class<T> t,String hql){
		List<String> alias = filterAlias(hql);
		List<Object[]> results=this.queryByHql(hql);
		List<T> list=this.reflectSetMethod(results, t, alias);
		return list;
	}

	public <T> List<T> queryVoByHql(Class<T> t,String hql,Map paramMap){
		List<String> alias = filterAlias(hql);
		List<Object[]> results=this.queryByHql(hql,paramMap);
		List<T> list=this.reflectSetMethod(results, t, alias);
		return list;
	}
	
	public <T> List<T> queryVoByHql(Class<T> t,String hql,Map paramMap,Integer start,Integer pageSize){
		List<String> alias = filterAlias(hql);
		List<Object[]> results=this.queryByHql(hql,paramMap,start,pageSize);
		List<T> list=this.reflectSetMethod(results, t, alias);
		return list;
	}
	
	public <T> List<T> queryVoBySql(Class<T> t,String sql){
		List<String> alias = filterAlias(sql);
		List<Object[]> results=this.queryByHql(sql);
		List<T> list=this.reflectSetMethod(results, t, alias);
		return list;
	}
	

	public <T> List<T> queryVoBySql(Class<T> t,String sql,Map paramMap){
		List<String> alias = filterAlias(sql);
		List<Object[]> results=this.queryBySql(sql, paramMap, null);
		List<T> list=this.reflectSetMethod(results, t, alias);
		return list;
	}

	
	public <T> List<T> queryVoBySql(Class<T> t,String sql,Map paramMap,Integer start,Integer pageSize){
		List<String> alias = filterAlias(sql);
		List<Object[]> results=this.queryBySql(sql, paramMap,start,pageSize, null);
		List<T> list=this.reflectSetMethod(results, t, alias);
		return list;
	}
	
	
	public static void main(String[] args){
		BaseDao baseDao = new BaseDao(); 
//		String sql = "select * from order where 1=1 and [prize=#obj1#] and [id=#id#] and type like #%aaa%# order by order.id [#orderType#]";
		String sql ="select tt.id as id," +
				"       tt.startnum as startnum," + 
				"       tt.endnum as endnum," + 
				"       tt.costper as costper, " + 
				"       tt.pagecount as pagecount," + 
				"       tt.costtotal as costtotal," + 
				"       tt.status as status," + 
				"       tt.createtime as createTime," + 
				"       tt.creator as creaor," + 
				"       f.bill_start_num as bsnum," + 
				"       f.bill_end_num as benum," + 
				"       nvl(f.bill_end_num - f.bill_start_num + 1,0) as stotal," + 
				"       (f.bill_end_num - f.bill_start_num + 1) * tt.costper as coll," + 
				"       tt.pagecount - (f.bill_end_num - f.bill_start_num + 1) as wwww," + 
				"       tt.costtotal - (f.bill_end_num - f.bill_start_num + 1) * tt.costper as popo," + 
				"       round((tt.pagecount - nvl(f.bill_end_num - f.bill_start_num + 1, 0)) / tt.pagecount,3) * 100 ||'%' rate as aaa" + 
				"  from (SELECT a.id," + 
				"           	a.start_num startNum," + 
				"               a.end_num endNum," + 
				"               a.cost_per_bill costPer," + 
				"               a.page_count pageCount," + 
				"               a.cost_total costTotal," + 
				"               a.status," + 
				"               a.create_time createTime," + 
				"               b.user_name creator" + 
				"          FROM tbl_bill a, tbl_user b" + 
				"         WHERE a.creator_id = b.id" + 
				"           AND a.del_tag = '0'" + 
				
				" and [to_number(a.start_num) <= to_number(#billNum#)]" +
				" and [to_number(#billNum#) <=to_number(a.end_num)]" +
				" and [b.true_name =#userName#]" + 
				" and [b.user_name like #%userCode%#]" +
				" and [a.create_time >= #startTime#]" +
				" and [a.create_time <= #endTime#]" +
				" and [a.status =#status#]" + 
				
				"                          ) tt" + 
				"    left join tbl_bill_submit f on tt.id = f.bill_id" + 
				"                               and f.del_tag = '0'" + 
				" order by tt.createtime desc";
		List results=new ArrayList();
		Map<String,Object> map=new HashMap<String,Object>();
//		map.put("lotID", "122");
//		map.put("carNum", "苏B123123");
//		map.put("startTime", new Date());
//		map.put("endTime", new Date());
//		map.put("orderType", "desc");
		System.out.println(baseDao.filterAlias(sql));
//		System.out.println(baseDao.filterQuery(sql, map));
//		System.out.println(results);

	}

	

}
